/*
 * FeeDAO.java
 *
 * Created on March 22, 2007, 1:45 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.Cache;
import edu.byu.isys413.group1E.ConnectionPool;
import edu.byu.isys413.group1E.ConnectionPoolException;
import edu.byu.isys413.group1E.DataException;
import edu.byu.isys413.group1E.GUID;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * @author Jon Tehero
 */
public class FeeDAO extends RevenueSourceDAO{
    private static FeeDAO instance = null;
    
//******************************************************************************
//   SINGLETON PATTERN
//******************************************************************************
    /** Creates a new instance of FeeDAO */
    public FeeDAO() {
    }
    
    /**Retrieves an instance of the TransactionDAO object**/
    public static synchronized FeeDAO getInstance() {
        if (instance == null) {
            instance = new FeeDAO();
        }
        return instance;
    }
    
//******************************************************************************
//   CREATE
//******************************************************************************
    public RevenueSourceBO create() throws Exception{
        FeeBO fee = new FeeBO(GUID.generate());
        fee.setType("fee");
        Cache c = Cache.getInstance();
        c.put(fee.getRevSrcID(), fee);
        return fee;
    }
    
//******************************************************************************
//   READ - Incorporates 2 Read Methods (1 for external use, 1 for internal),
//          and 1 ReadAll Method
//******************************************************************************
    public RevenueSourceBO read(String id) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        RevenueSourceBO fee = (FeeBO)c.get(id);
        if (fee != null){
            return fee;                                                        // if so, return it immediately
        }
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            fee = (FeeBO)this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id = " + id, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return fee;
    }
    
    public synchronized RevenueSourceBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        FeeBO fee = (FeeBO)c.get(id);
        if (fee != null){
            return fee;                                                        // if so, return it immediately
        }
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        fee = new FeeBO(id);
        c.put(id, fee);
        fee = (FeeBO)super.read(id, conn);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM fee WHERE RevenueSourceID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            fee.setRentalID(rs.getString("RentalID"));
            fee.setEmployee(rs.getString("Employee"));
            fee.setAmount(rs.getDouble("Amount"));
            if(rs.getInt("Waived") == 0){fee.setWaived(false);}else{fee.setWaived(true);}
            if(rs.getInt("Damagaed") == 0){fee.setDamaged(false);}else{fee.setDamaged(true);}
            fee.setDaysLate(rs.getInt("DaysLate"));
            fee.setDescription(rs.getString("Description"));
            fee.setAlreadyInDB(true);
            fee.setIsDirty(false);
            // save to the cache
            c.put(fee.getRevSrcID(), fee);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return fee;
    }
    
    public synchronized ArrayList<RevenueSourceBO> readAll() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<RevenueSourceBO> list = new ArrayList<RevenueSourceBO>();
        //now build the query
        String query = "SELECT * FROM fee";
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        Cache c = Cache.getInstance();
        
        //extract the elements from the result set and build a list of results
        //build the object from the result set
        while (rs.next()) {
            FeeBO fee = new FeeBO(rs.getString("RevenueSourceID"));
            c.put(fee.getRevSrcID(), fee);
            fee = (FeeBO)super.read(rs.getString("RevenueSourceID"), conn);
            fee.setRentalID(rs.getString("RentalID"));
            fee.setEmployee(rs.getString("Employee"));
            fee.setAmount(rs.getDouble("Amount"));
            if(rs.getInt("Waived") == 0){fee.setWaived(false);}else{fee.setWaived(true);}
            if(rs.getInt("Damagaed") == 0){fee.setDamaged(false);}else{fee.setDamaged(true);}
            fee.setDaysLate(rs.getInt("DaysLate"));
            fee.setDescription(rs.getString("Description"));
            fee.setAlreadyInDB(true);
            fee.setIsDirty(false);
            // save to the list
            list.add(fee);
        }//while
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return list;
    }
    
//******************************************************************************
//   UPDATE - Incorporates 1 Insert, & 1 Update methods
//******************************************************************************
    synchronized void insert(RevenueSourceBO fee, Connection conn) throws SQLException, DataException {
        //update the DB
        FeeBO BO = (FeeBO)fee;
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO fee (revenueSourceID, quantity)" +
                "VALUES (?, ?)");
        pstmt.setString(1, BO.getRevSrcID());
        pstmt.setString(2, BO.getRentalID());
        pstmt.setString(3, BO.getEmployee());
        pstmt.setDouble(4, BO.getAmount());
        if(BO.isWaived() == true){pstmt.setInt(5, 1);}else{pstmt.setInt(5, 0);}
        if(BO.isDamaged() == true){pstmt.setInt(6, 1);}else{pstmt.setInt(6, 0);}
        pstmt.setInt(7, BO.getDaysLate());
        pstmt.setString(8, BO.getDescription());
        pstmt.executeUpdate();
        pstmt.close();
        BO.setAlreadyInDB(true);
    }//end insert
    
    protected void update(RevenueSourceBO fee, Connection conn) throws SQLException, DataException {
        //update the DB
        FeeBO BO = (FeeBO)fee;
        PreparedStatement pstmt = conn.prepareStatement("UPDATE fee SET " +
                "Rental_ID = ?, Employee = ?, Amount = ?, Waived = ?, " +
                "Damaged = ?, DaysLate = ?, Description = ? WHERE revenueSourceID = ?");
        
        pstmt.setString(1, BO.getRentalID());
        pstmt.setString(2, BO.getEmployee());
        pstmt.setDouble(3, BO.getAmount());
        if(BO.isWaived() == true){pstmt.setInt(4, 1);}else{pstmt.setInt(4, 0);}
        if(BO.isDamaged() == true){pstmt.setInt(5, 1);}else{pstmt.setInt(5, 0);}
        pstmt.setInt(6, BO.getDaysLate());
        pstmt.setString(7, BO.getDescription());
        pstmt.setString(8, BO.getRevSrcID());
        pstmt.executeUpdate();
        pstmt.close();
        BO.setAlreadyInDB(true);
    }// end update
    
//******************************************************************************
//   DELETE - Does not incorporate any methods
//******************************************************************************
    /**
     * Any and all delete methods were intentionally left out of this
     * program becuase we specifically DO NOT want to give the users the
     * ability to delete ANYTHING.  All records will remain on file.
     * Archival options would recommended over deleting.
     **/
}
